/*
 * 
 * 
 * 
 */
package com.hboxs.asl.dao.impl;

import com.hboxs.asl.Filter;
import com.hboxs.asl.Page;
import com.hboxs.asl.Pageable;
import com.hboxs.asl.dao.OrderDao;
import com.hboxs.asl.entity.Member;
import com.hboxs.asl.entity.Order;
import com.hboxs.asl.entity.Order.OrderStatus;
import com.hboxs.asl.entity.Order.PaymentStatus;
import com.hboxs.asl.entity.Order.ShippingStatus;
import com.hboxs.asl.entity.OrderItem;
import com.hboxs.asl.entity.Product;
import org.springframework.stereotype.Repository;

import javax.persistence.FlushModeType;
import javax.persistence.LockModeType;
import javax.persistence.NoResultException;
import javax.persistence.criteria.*;
import java.math.BigDecimal;
import java.util.Collections;
import java.util.Date;
import java.util.List;

/**
 * Dao - 订单
 */
@Repository("orderDaoImpl")
public class OrderDaoImpl extends BaseDaoImpl<Order, Long> implements OrderDao {

    public Order findBySn(String sn) {
        if (sn == null) {
            return null;
        }
        String jpql = "select orders from Order orders where lower(orders.sn) = lower(:sn)";
        try {
            return entityManager.createQuery(jpql, Order.class).setFlushMode(FlushModeType.COMMIT).setParameter("sn", sn).getSingleResult();
        } catch (NoResultException e) {
            return null;
        }
    }

    public List<Order> findList(Member member, Integer count, List<Filter> filters, List<com.hboxs.asl.Order> orders) {
        if (member == null) {
            return Collections.emptyList();
        }
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<Order> criteriaQuery = criteriaBuilder.createQuery(Order.class);
        Root<Order> root = criteriaQuery.from(Order.class);
        criteriaQuery.select(root);
        criteriaQuery.where(criteriaBuilder.equal(root.get("member"), member));
        return super.findList(criteriaQuery, null, count, filters, orders);
    }

    public Page<Order> findPage(Member member, Pageable pageable) {
        if (member == null) {
            return new Page<Order>(Collections.<Order>emptyList(), 0, pageable);
        }
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<Order> criteriaQuery = criteriaBuilder.createQuery(Order.class);
        Root<Order> root = criteriaQuery.from(Order.class);
        criteriaQuery.select(root);
        criteriaQuery.where(criteriaBuilder.equal(root.get("member"), member));
        return super.findPage(criteriaQuery, pageable);
    }

    public Page<Order> findPage(OrderStatus orderStatus, PaymentStatus paymentStatus, ShippingStatus shippingStatus, Boolean hasExpired, Pageable pageable) {
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<Order> criteriaQuery = criteriaBuilder.createQuery(Order.class);
        Root<Order> root = criteriaQuery.from(Order.class);
        criteriaQuery.select(root);
        Predicate restrictions = criteriaBuilder.conjunction();
        if (orderStatus != null) {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("orderStatus"), orderStatus));
        }
        if (paymentStatus != null) {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("paymentStatus"), paymentStatus));
        }
        if (shippingStatus != null) {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("shippingStatus"), shippingStatus));
        }
        if (hasExpired != null) {
            if (hasExpired) {
                restrictions = criteriaBuilder.and(restrictions, root.get("expire").isNotNull(), criteriaBuilder.lessThan(root.<Date>get("expire"), new Date()));
            } else {
                restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.or(root.get("expire").isNull(), criteriaBuilder.greaterThanOrEqualTo(root.<Date>get("expire"), new Date())));
            }
        }
        criteriaQuery.where(restrictions);
        return super.findPage(criteriaQuery, pageable);
    }

    public Long count(OrderStatus orderStatus, PaymentStatus paymentStatus, ShippingStatus shippingStatus, Boolean hasExpired) {
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<Order> criteriaQuery = criteriaBuilder.createQuery(Order.class);
        Root<Order> root = criteriaQuery.from(Order.class);
        criteriaQuery.select(root);
        Predicate restrictions = criteriaBuilder.conjunction();
        if (orderStatus != null) {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("orderStatus"), orderStatus));
        }
        if (paymentStatus != null) {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("paymentStatus"), paymentStatus));
        }
        if (shippingStatus != null) {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("shippingStatus"), shippingStatus));
        }
        if (hasExpired != null) {
            if (hasExpired) {
                restrictions = criteriaBuilder.and(restrictions, root.get("expire").isNotNull(), criteriaBuilder.lessThan(root.<Date>get("expire"), new Date()));
            } else {
                restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.or(root.get("expire").isNull(), criteriaBuilder.greaterThanOrEqualTo(root.<Date>get("expire"), new Date())));
            }
        }
        criteriaQuery.where(restrictions);
        return super.count(criteriaQuery, null);
    }

    public Page<Order> findPage(Member member, OrderStatus orderStatus, PaymentStatus paymentStatus, ShippingStatus shippingStatus, Boolean hasExpired, Boolean hasReview, Boolean hasApplyReturn, Pageable pageable) {
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<Order> criteriaQuery = criteriaBuilder.createQuery(Order.class);
        Root<Order> root = criteriaQuery.from(Order.class);
        criteriaQuery.groupBy(root.get("id"));
        criteriaQuery.select(root);
        Predicate restrictions = criteriaBuilder.conjunction();
        if (member != null) {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("member"), member));
        }
        if (orderStatus != null) {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("orderStatus"), orderStatus));
        }
        if (paymentStatus != null) {
            //退换货的情况加上已退款的
            if (hasApplyReturn!=null && hasApplyReturn){
                restrictions = criteriaBuilder.and(
                        restrictions,
                        criteriaBuilder.and(
                                criteriaBuilder.notEqual(root.get("paymentStatus"), PaymentStatus.unpaid),
                                criteriaBuilder.notEqual(root.get("paymentStatus"), PaymentStatus.partialPayment)
                        )
                );
            } else {
                restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("paymentStatus"), paymentStatus));
            }
        }
        if (shippingStatus != null) {
            //退换货的情况,筛选出申请退款，或者直接退款的情况
            if (hasApplyReturn!=null && hasApplyReturn){
                Join<Order, OrderItem> orderItems = root.join("orderItems");
                restrictions = criteriaBuilder.and(
                        restrictions,
                        criteriaBuilder.or(
                                criteriaBuilder.or(criteriaBuilder.equal(root.get("shippingStatus"),ShippingStatus.returned),criteriaBuilder.equal(orderItems.get("hasApplyReturn"),true)),
                                criteriaBuilder.or(criteriaBuilder.equal(root.get("shippingStatus"),ShippingStatus.partialReturns),criteriaBuilder.equal(orderItems.get("hasApplyReturn"),true))
                        )
                );
            }else{
                restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("shippingStatus"), shippingStatus));
            }
        }
        if (hasExpired != null) {
            if (hasExpired) {
                restrictions = criteriaBuilder.and(restrictions, root.get("expire").isNotNull(), criteriaBuilder.lessThan(root.<Date>get("expire"), new Date()));
            } else {
                restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.or(root.get("expire").isNull(), criteriaBuilder.greaterThanOrEqualTo(root.<Date>get("expire"), new Date())));
            }
        }
        if (hasReview != null){
            Join<Order, OrderItem> orderItems = root.join("orderItems");
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(orderItems.get("hasReview"), hasReview));
        }
        //false为未申请
        if (hasApplyReturn!=null && !hasApplyReturn){
            Join<Order, OrderItem> orderItems = root.join("orderItems");
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(orderItems.get("hasApplyReturn"), hasApplyReturn));
        }
        //已完成即带评价订单要包含已退货，部分退货，申请退货的情况
        /*if (OrderStatus.completed.equals(orderStatus)){
            restrictions = criteriaBuilder.or(restrictions,criteriaBuilder.equal(root.get("shippingStatus"),ShippingStatus.partialReturns));
            restrictions = criteriaBuilder.or(restrictions,criteriaBuilder.equal(root.get("shippingStatus"),ShippingStatus.returned));
            Join<Order, OrderItem> orderItems = root.join("orderItems");
            restrictions = criteriaBuilder.or(restrictions,criteriaBuilder.equal(orderItems.get("hasApplyReturn"),true));
        }*/
        criteriaQuery.where(restrictions);
        return super.findPageGroup(criteriaQuery, pageable);
    }

    public Long count(Member member, OrderStatus orderStatus, PaymentStatus paymentStatus, ShippingStatus shippingStatus, Boolean hasExpired , Boolean hasReview, Boolean hasApplyReturn) {
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<Order> criteriaQuery = criteriaBuilder.createQuery(Order.class);
        Root<Order> root = criteriaQuery.from(Order.class);
        criteriaQuery.select(root);
        Predicate restrictions = criteriaBuilder.conjunction();
        if (member != null) {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("member"), member));
        }
        if (orderStatus != null) {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("orderStatus"), orderStatus));
        }
        if (paymentStatus != null) {
            if (hasApplyReturn!=null && hasApplyReturn){
                restrictions = criteriaBuilder.and(
                        restrictions,
                        criteriaBuilder.and(
                                criteriaBuilder.notEqual(root.get("paymentStatus"), PaymentStatus.unpaid),
                                criteriaBuilder.notEqual(root.get("paymentStatus"), PaymentStatus.partialPayment)
                        )
                );
            } else {
                restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("paymentStatus"), paymentStatus));
            }
        }
        if (shippingStatus != null) {
            if (hasApplyReturn!=null && hasApplyReturn){
                Join<Order, OrderItem> orderItems = root.join("orderItems");
                restrictions = criteriaBuilder.and(
                        restrictions,
                        criteriaBuilder.or(
                                criteriaBuilder.or(criteriaBuilder.equal(root.get("shippingStatus"),ShippingStatus.returned),criteriaBuilder.equal(orderItems.get("hasApplyReturn"),true)),
                                criteriaBuilder.or(criteriaBuilder.equal(root.get("shippingStatus"),ShippingStatus.partialReturns),criteriaBuilder.equal(orderItems.get("hasApplyReturn"),true))
                        )
                );
            }else{
                restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("shippingStatus"), shippingStatus));
            }
        }
        if (hasExpired != null) {
            if (hasExpired) {
                restrictions = criteriaBuilder.and(restrictions, root.get("expire").isNotNull(), criteriaBuilder.lessThan(root.<Date>get("expire"), new Date()));
            } else {
                restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.or(root.get("expire").isNull(), criteriaBuilder.greaterThanOrEqualTo(root.<Date>get("expire"), new Date())));
            }
        }
        if (hasReview != null){
            Join<Order, OrderItem> orderItems = root.join("orderItems");
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(orderItems.get("hasReview"), hasReview));
        }
        if (hasApplyReturn!=null && !hasApplyReturn){
            //进入这里不需要退换货或者已申请退换货的，
            Join<Order, OrderItem> orderItems = root.join("orderItems");
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(orderItems.get("hasApplyReturn"), hasApplyReturn));
        }
        /*if (OrderStatus.completed.equals(orderStatus)){
            restrictions = criteriaBuilder.or(restrictions,criteriaBuilder.equal(root.get("shippingStatus"),ShippingStatus.partialReturns));
            restrictions = criteriaBuilder.or(restrictions,criteriaBuilder.equal(root.get("shippingStatus"),ShippingStatus.returned));
            Join<Order, OrderItem> orderItems = root.join("orderItems");
            restrictions = criteriaBuilder.or(restrictions,criteriaBuilder.equal(orderItems.get("hasApplyReturn"),true));
        }*/
        criteriaQuery.where(restrictions);
        return super.count(criteriaQuery, null);
    }

    public Long waitingPaymentCount(Member member) {
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<Order> criteriaQuery = criteriaBuilder.createQuery(Order.class);
        Root<Order> root = criteriaQuery.from(Order.class);
        criteriaQuery.select(root);
        Predicate restrictions = criteriaBuilder.conjunction();
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.notEqual(root.get("orderStatus"), OrderStatus.completed), criteriaBuilder.notEqual(root.get("orderStatus"), OrderStatus.cancelled));
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.or(criteriaBuilder.equal(root.get("paymentStatus"), PaymentStatus.unpaid), criteriaBuilder.equal(root.get("paymentStatus"), PaymentStatus.partialPayment)));
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.or(root.get("expire").isNull(), criteriaBuilder.greaterThanOrEqualTo(root.<Date>get("expire"), new Date())));
        if (member != null) {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("member"), member));
        }
        criteriaQuery.where(restrictions);
        return super.count(criteriaQuery, null);
    }

    public Page<Order> findWaitingPaymentPage(Member member, Pageable pageable) {
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<Order> criteriaQuery = criteriaBuilder.createQuery(Order.class);
        Root<Order> root = criteriaQuery.from(Order.class);
        criteriaQuery.select(root);
        Predicate restrictions = criteriaBuilder.conjunction();
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.notEqual(root.get("orderStatus"), OrderStatus.completed), criteriaBuilder.notEqual(root.get("orderStatus"), OrderStatus.cancelled));
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.or(criteriaBuilder.equal(root.get("paymentStatus"), PaymentStatus.unpaid), criteriaBuilder.equal(root.get("paymentStatus"), PaymentStatus.partialPayment)));
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.or(root.get("expire").isNull(), criteriaBuilder.greaterThanOrEqualTo(root.<Date>get("expire"), new Date())));
        if (member != null) {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("member"), member));
        }
        criteriaQuery.where(restrictions);
        return super.findPage(criteriaQuery, pageable);
    }

    public Long waitingShippingCount(Member member) {
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<Order> criteriaQuery = criteriaBuilder.createQuery(Order.class);
        Root<Order> root = criteriaQuery.from(Order.class);
        criteriaQuery.select(root);
        Predicate restrictions = criteriaBuilder.conjunction();
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.notEqual(root.get("orderStatus"), OrderStatus.completed), criteriaBuilder.notEqual(root.get("orderStatus"), OrderStatus.cancelled), criteriaBuilder.equal(root.get("paymentStatus"), PaymentStatus.paid), criteriaBuilder.equal(root.get("shippingStatus"), ShippingStatus.unshipped));
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.or(root.get("expire").isNull(), criteriaBuilder.greaterThanOrEqualTo(root.<Date>get("expire"), new Date())));
        if (member != null) {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("member"), member));
        }
        criteriaQuery.where(restrictions);
        return super.count(criteriaQuery, null);
    }

    @Override
    public Page<Order> findWaitingShippingPage(Member member, Pageable pageable) {
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<Order> criteriaQuery = criteriaBuilder.createQuery(Order.class);
        Root<Order> root = criteriaQuery.from(Order.class);
        criteriaQuery.select(root);
        Predicate restrictions = criteriaBuilder.conjunction();
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.notEqual(root.get("orderStatus"), OrderStatus.completed), criteriaBuilder.notEqual(root.get("orderStatus"), OrderStatus.cancelled), criteriaBuilder.equal(root.get("paymentStatus"), PaymentStatus.paid), criteriaBuilder.equal(root.get("shippingStatus"), ShippingStatus.unshipped));
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.or(root.get("expire").isNull(), criteriaBuilder.greaterThanOrEqualTo(root.<Date>get("expire"), new Date())));
        if (member != null) {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("member"), member));
        }
        criteriaQuery.where(restrictions);
        return super.findPage(criteriaQuery, pageable);
    }

    @Override
    public Page<Order> receivedPage(Member member, Pageable pageable) {
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<Order> criteriaQuery = criteriaBuilder.createQuery(Order.class);
        Root<Order> root = criteriaQuery.from(Order.class);
        criteriaQuery.select(root);
        Predicate restrictions = criteriaBuilder.conjunction();
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.notEqual(root.get("orderStatus"), OrderStatus.completed), criteriaBuilder.notEqual(root.get("orderStatus"), OrderStatus.cancelled), criteriaBuilder.equal(root.get("paymentStatus"), PaymentStatus.paid), criteriaBuilder.equal(root.get("shippingStatus"), ShippingStatus.shipped));
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.or(root.get("expire").isNull(), criteriaBuilder.greaterThanOrEqualTo(root.<Date>get("expire"), new Date())));
        if (member != null) {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("member"), member));
        }
        criteriaQuery.where(restrictions);
        return super.findPage(criteriaQuery, pageable);
    }

    @Override
    public Page<Order> pendingEvaluationPage(Member member, Pageable pageable) {
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<Order> criteriaQuery = criteriaBuilder.createQuery(Order.class);
        Root<Order> root = criteriaQuery.from(Order.class);
        criteriaQuery.select(root);
        Predicate restrictions = criteriaBuilder.conjunction();
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("orderStatus"), OrderStatus.completed), criteriaBuilder.notEqual(root.get("orderStatus"), OrderStatus.cancelled), criteriaBuilder.equal(root.get("paymentStatus"), PaymentStatus.paid), criteriaBuilder.equal(root.get("shippingStatus"), ShippingStatus.shipped));
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.or(root.get("expire").isNull(), criteriaBuilder.greaterThanOrEqualTo(root.<Date>get("expire"), new Date())));
        if (member != null) {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("member"), member));
        }
        criteriaQuery.where(restrictions);
        return super.findPage(criteriaQuery, pageable);
    }

    public BigDecimal getSalesAmount(Date beginDate, Date endDate) {
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<BigDecimal> criteriaQuery = criteriaBuilder.createQuery(BigDecimal.class);
        Root<Order> root = criteriaQuery.from(Order.class);
        criteriaQuery.select(criteriaBuilder.sum(root.<BigDecimal>get("amountPaid")));
        Predicate restrictions = criteriaBuilder.conjunction();
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("orderStatus"), OrderStatus.completed));
        if (beginDate != null) {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.greaterThanOrEqualTo(root.<Date>get("createDate"), beginDate));
        }
        if (endDate != null) {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.lessThanOrEqualTo(root.<Date>get("createDate"), endDate));
        }
        criteriaQuery.where(restrictions);
        return entityManager.createQuery(criteriaQuery).setFlushMode(FlushModeType.COMMIT).getSingleResult();
    }


    public BigDecimal getPaySalesAmount(Date beginDate, Date endDate) {
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<BigDecimal> criteriaQuery = criteriaBuilder.createQuery(BigDecimal.class);
        Root<Order> root = criteriaQuery.from(Order.class);
        criteriaQuery.select(criteriaBuilder.sum(root.<BigDecimal>get("amountPaid")));
        Predicate restrictions = criteriaBuilder.conjunction();
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("paymentStatus"), PaymentStatus.paid));
        if (beginDate != null) {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.greaterThanOrEqualTo(root.<Date>get("createDate"), beginDate));
        }
        if (endDate != null) {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.lessThanOrEqualTo(root.<Date>get("createDate"), endDate));
        }
        criteriaQuery.where(restrictions);
        return entityManager.createQuery(criteriaQuery).setFlushMode(FlushModeType.COMMIT).getSingleResult();
    }



    public Integer getSalesVolume(Date beginDate, Date endDate) {
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<Integer> criteriaQuery = criteriaBuilder.createQuery(Integer.class);
        Root<Order> root = criteriaQuery.from(Order.class);
        criteriaQuery.select(criteriaBuilder.sum(root.join("orderItems").<Integer>get("shippedQuantity")));
        Predicate restrictions = criteriaBuilder.conjunction();
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("orderStatus"), OrderStatus.completed));
        if (beginDate != null) {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.greaterThanOrEqualTo(root.<Date>get("createDate"), beginDate));
        }
        if (endDate != null) {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.lessThanOrEqualTo(root.<Date>get("createDate"), endDate));
        }
        criteriaQuery.where(restrictions);
        return entityManager.createQuery(criteriaQuery).setFlushMode(FlushModeType.COMMIT).getSingleResult();
    }

    public void releaseStock() {
        String jpql = "select orders from Order orders where orders.isAllocatedStock = :isAllocatedStock and orders.expire is not null and orders.expire <= :now";
        List<Order> orders = entityManager.createQuery(jpql, Order.class).setParameter("isAllocatedStock", true).setParameter("now", new Date()).getResultList();
        if (orders != null) {
            for (Order order : orders) {
                if (order != null && order.getOrderItems() != null) {
                    for (OrderItem orderItem : order.getOrderItems()) {
                        if (orderItem != null) {
                            Product product = orderItem.getProduct();
                            if (product != null) {
                                entityManager.lock(product, LockModeType.PESSIMISTIC_WRITE);
                                product.setAllocatedStock(product.getAllocatedStock() - (orderItem.getQuantity() - orderItem.getShippedQuantity()));
                            }
                        }
                    }
                    order.setIsAllocatedStock(false);
                }
            }
        }
    }

    @Override
    public Long count(OrderStatus orderStatus, Member member, Product product, Boolean isAllocatedStock, Boolean hasExpired) {
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<Order> criteriaQuery = criteriaBuilder.createQuery(Order.class);
        Root<Order> root = criteriaQuery.from(Order.class);
        criteriaQuery.select(root);
        Predicate restrictions = criteriaBuilder.conjunction();
        if (orderStatus != null) {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("orderStatus"), orderStatus));
        }
        if (member != null) {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("member"), member));
        }
        if (product != null) {

//            Subquery<Product> productSubquery = criteriaQuery.subquery(Product.class);
//            Root<Product> productSubqueryRoot = productSubquery.from(Product.class);
//            productSubquery.select(productSubqueryRoot);
//            productSubquery.where(criteriaBuilder.equal(productSubqueryRoot.get("product"), product));

            Subquery<OrderItem> orderItemSubquery = criteriaQuery.subquery(OrderItem.class);
            Root<OrderItem> orderItemSubqueryRoot = orderItemSubquery.from(OrderItem.class);
            orderItemSubquery.select(orderItemSubqueryRoot);
            orderItemSubquery.where(criteriaBuilder.equal(orderItemSubqueryRoot.get("order"), root), criteriaBuilder.equal(orderItemSubqueryRoot.get("product"), product));
//            orderItemSubquery.where(criteriaBuilder.equal(orderItemSubqueryRoot.get("order"), root), criteriaBuilder.in(orderItemSubqueryRoot.get("product")).value(productSubquery));
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.exists(orderItemSubquery));
        }
        if (isAllocatedStock != null) {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("isAllocatedStock"), isAllocatedStock));
        }
        if (hasExpired != null) {
            if (hasExpired) {
                restrictions = criteriaBuilder.and(restrictions, root.get("expire").isNotNull(), criteriaBuilder.lessThanOrEqualTo(root.<Date>get("expire"), new Date()));
            } else {
                restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.or(root.get("expire").isNull(), criteriaBuilder.greaterThan(root.<Date>get("expire"), new Date())));
            }
        }
        criteriaQuery.where(restrictions);
        return super.count(criteriaQuery, null);
    }

}